package com.apestech.api.condition;

import com.apestech.api.xml.esb.EsbUtil;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.dictionary.modul.ImportedKey;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.LocalVariable;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.xml.plugin.modul.SpecialField;

import java.util.ArrayList;

public class ConditionTableQrySql extends StringUtil{

    public class Auth {
        private String field;
        private String role;

        public Auth(String field, String role){
            this.field=field;
            this.role=role;
        }

        public String getField() {
            return field;
        }

        public String getRole() {
            return role;
        }
    }

    private String tableName;
    private String tableitemName;
    private String tableexecName;
    private String tableimpName;
    private String tablestopName;
    private SqlSession session;
    private Table ztab;
    private Table itab;
    private Table etab;
    private Table imptab;
    private Table stoptab;
    private ArrayList<String> zkey;
    private ArrayList<String> ikey;
    private ArrayList<String> ekey;
    private ArrayList<String> impkey;
    private ArrayList<String> stopkey;
    private String ztabSqlSingle;
    private String ztabSql;
    private String mxtabSql;
    private String exectabSql;
    private String imptabSql;
    private String stoptabSql;
    private String billQrySql;
    private SpecialField.Field sp;
    private JSONObject postJSON;
    private JSONObject authjson;
    private JSONArray gridheader;
    private JSONArray listField;
    private JSONObject head;
    private ArrayList<String> dateField;
    private JSONObject formJSON;

    public ConditionTableQrySql(String tableName, SqlSession session) throws Exception {
        this.tableName=tableName;
        tableitemName=tableName+"_ITEM";
        tableexecName=tableName+"_EXEC";
        tableimpName=tableName+"_EXEC_I";
        tablestopName=tableName+"_EXEC_STOP";
        this.session=session;
        ztab=session.getDictTable(this.tableName);
        if(ztab==null){
            new Exception("当前表名["+tableName+"]不正确，请输入正确的表名!");
        }
        itab=session.getDictTable(tableitemName);
        etab=session.getDictTable(tableexecName);
        imptab=session.getDictTable(tableimpName);
        stoptab=session.getDictTable(tablestopName);
        zkey=ztab.getField().getKeylist();
        ikey=itab.getField().getKeylist();
        ekey=etab.getField().getKeylist();
        if(stoptab!=null){
            stopkey=stoptab.getField().getKeylist();
        }
        if(imptab!=null){
            impkey=imptab.getField().getKeylist();
        }
        sp=session.getSpecialField();
        authjson=new JSONObject();
        listField=new JSONArray();
        head=new JSONObject();
        dateField=new ArrayList<String>();
    }

    public void setFormJSON(JSONObject formJSON) {
        this.formJSON = formJSON;
    }

    public JSONArray getGridheader() {
        return gridheader;
    }

    public void setGridheader(JSONArray gridheader) {
        this.gridheader = gridheader;
    }

    public void setPostJSON(JSONObject postJSON) {
        this.postJSON = postJSON;
        if(postJSON!=null && postJSON.size()==0){
            this.postJSON=null;
        }
    }

    public JSONArray getListField() {
        return listField;
    }

    public JSONObject getHead() {
        return head;
    }

    public ArrayList<String> getDateField() {
        return dateField;
    }

    public void setDateField(ArrayList<String> dateField) {
        this.dateField = dateField;
    }

    public void getImportInfo() throws Exception {
        int len=zkey.size();
        for(int i=0; i<len; i++){
            String k=zkey.get(i);
            String remark=ztab.getRemarks(k);
            String dbtype=ztab.getDbType(k);
            if(dbtype==null){
                continue;
            }
            if((dbtype.indexOf("TIME")>=0 || dbtype.indexOf("DATE")>=0)){
                dateField.add(k);
            }
            String fid=StringUtil.getSpiltId(remark);
            String list=StringUtil.getSplitList(remark);
            if("CONTROL_TYPE".equals(k)){
                list="0:普通;1:区间;2:是否";
            } else if("PRIORITY".equals(k)){
                list="0:普通;1:优先";
            }  else if(sp.getState().equals(k)){
                list="sent:制单;exec:执行;stop:终止";
            }
            if(list!=null && !"".equals(list) && list.length()>1){
                JSONObject m=new JSONObject();
                m.put("field", k);
                m.put("list", list);
                listField.add(m);
                fid=fid+"编码";
            }
            if(ztab.getfTab(k)!=null){
                fid=fid+"编码";
            }
            if(!sp.getTimestamp().equals(k) && !sp.getVersion().equals(k) && !sp.getScene().equals(k) && !sp.getBilltype().equals(k) && !"SYS_RQ".equals(k)){
                head.put(fid, k);
            }
        }
        len=ikey.size();
        for(int i=0; i<len; i++){
            String k=ikey.get(i);
            String remark=itab.getRemarks(k);
            String fid=StringUtil.getSpiltId(remark);
            String list=StringUtil.getSplitList(remark);
            if(list!=null && !"".equals(list) && list.length()>1){
                JSONObject m=new JSONObject();
                m.put("field", k);
                m.put("list", list);
                listField.add(m);
                fid=fid+"编码";
            }
            if(itab.getfTab(k)!=null){
                fid=fid+"编码";
            }
            head.put(fid,k);
        }
    }

    private String getRoleString(String sqlId, String purpose){
        String role="";
        if(postJSON!=null) {
            JSONObject m= (JSONObject) authjson.get(sqlId);
            if(m==null){
                return "";
            }
            ArrayList<String> klist=m.getKeylist();
            int len=klist.size();
            for(int i=0; i<len; i++){
                ArrayList<Auth> list= (ArrayList<Auth>) m.get(klist.get(i));
                int jlen=list.size();
                for(int j=0; j<jlen; j++){
                    String type=postJSON.getString(purpose);
                    if(type!=null){
                        if((","+type+",").indexOf(","+klist.get(i)+",")>=0){
                            role=role+list.get(j).getRole();
                        }
                    }
                }
            }
        }
        return role;
    }

    public String getbillQrySql() throws Exception {  //主表权限
        if(billQrySql==null){
            String role="";
            String select="";
            String dselect=" <if test=\"Z_ID!=null\"> AND EXISTS ( SELECT 1 FROM EXE_CONDITION_SUM Z WHERE Z.B_ID=A."+sp.getConditionId()+" AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND Z.ID LIKE '%'||#{Z_ID}||'%' ) </if>";
            int len=zkey.size();
            for(int i=0; i<len; i++){
                String k=zkey.get(i);
                if(!sp.getTimestamp().equals(k) && !sp.getVersion().equals(k) && !sp.getScene().equals(k) && !sp.getBilltype().equals(k) && !"SYS_RQ".equals(k)&& !"Z_ID".equals(k)){
                    ConFieldM f=new ConFieldM(k, ztab, session, "pp_search", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND ROWNUM=1 ) AS Z_ID ";
            len=ikey.size();
            for(int i=0; i<len; i++){
                String k=ikey.get(i);
                if(!zkey.contains(k) && !"SYS_RQ".equals(k)){
                    ConFieldM f=new ConFieldM(k, itab, session, "pp_search", "B", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            billQrySql="<rule from=\"A."+tableName+";B."+tableitemName+"\"/>"+role+" SELECT "+select+" FROM "+tableName+" A,"+tableitemName+" B  WHERE 1=1 AND A."+sp.getConditionId()+"=B."+sp.getConditionId()+dselect+" @WHERE@ ";
        }
        return getRoleString("billQry", "pp_search")+billQrySql;
    }

    public String getStoptabSql() throws Exception {  //主、从表权限
        if(stoptabSql==null){
            String select="";
            String dselect="<if test=\"Z_ID!=null\"> AND EXISTS ( SELECT 1 FROM EXE_CONDITION_SUM Z WHERE Z.B_ID=A."+sp.getConditionId()+" AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND Z.ID LIKE '%'||#{Z_ID}||'%' ) </if>";
            String role="";
            int len=stopkey.size();
            for(int i=0; i<len; i++){
                String k=stopkey.get(i);
                if(!"SYS_RQ".equals(k)){
                    ConFieldM f=new ConFieldM(k, stoptab, session, "pp_operate", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} ) AS Z_ID ";
            stoptabSql="<rule from=\"A."+tablestopName+"\" />"+role+" SELECT "+select+" FROM "+tablestopName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return getRoleString("stopTab", "pp_operate")+stoptabSql;
    }

    public String getimpTabSql() throws Exception {  //主、从表权限
        if(imptabSql==null){
            String select="";
            String role="";
            String dselect="<if test=\"Z_ID!=null\"> AND EXISTS ( SELECT 1 FROM EXE_CONDITION_SUM Z WHERE Z.B_ID=A."+sp.getConditionId()+" AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND Z.ID LIKE '%'||#{Z_ID}||'%' ) </if>";
            int len=impkey.size();
            for(int i=0; i<len; i++){
                String k=impkey.get(i);
                if(!"SYS_RQ".equals(k)){
                    ConFieldM f=new ConFieldM(k, imptab, session, "pp_operate", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} ) AS Z_ID ";
            imptabSql="<rule from=\"A."+tableimpName+"\" />"+role+" SELECT "+select+" FROM "+tableimpName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return getRoleString("impTab", "pp_operate")+imptabSql;
    }

    public String getexecTabSql() throws Exception {  //主、从表权限
        if(exectabSql==null){
            String select="";
            String role="";
            String dselect="<if test=\"Z_ID!=null\"> AND EXISTS ( SELECT 1 FROM EXE_CONDITION_SUM Z WHERE Z.B_ID=A."+sp.getConditionId()+" AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND Z.ID LIKE '%'||#{Z_ID}||'%' ) </if>";
            ekey.remove("Z_ID");
            int len=ekey.size();
            for(int i=0; i<len; i++){
                String k=ekey.get(i);
                if(!"SYS_RQ".equals(k)){
                    ConFieldM f=new ConFieldM(k, etab, session, "pp_search", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} ) AS Z_ID ";
            exectabSql="<rule from=\"A."+tableexecName+"\" />"+role+" SELECT "+select+" FROM "+tableexecName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return getRoleString("execTab", "pp_search")+exectabSql;
    }

    public String getzTabSql() throws Exception {  //主表权限
        if(ztabSql==null){
            String select="";
            String dselect="";
            String role="";
            for(String k: zkey){
                if(!sp.getTimestamp().equals(k) && !sp.getVersion().equals(k) && !sp.getScene().equals(k) && !sp.getBilltype().equals(k) && !"SYS_RQ".equals(k) && !"Z_ID".equals(k) ){
                    ConFieldM f=new ConFieldM(k, ztab, session, "pp_operate", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} ) AS Z_ID ";
            dselect=dselect+"<if test=\"Z_ID!=null\"> AND EXISTS ( SELECT 1 FROM EXE_CONDITION_SUM Z WHERE Z.B_ID=A."+sp.getConditionId()+" AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} AND Z.ID LIKE '%'||#{Z_ID}||'%' ) </if>";
            dselect=dselect+"<if test=\"ITEM!=null\"> AND EXISTS ( SELECT 1 FROM "+tableName+"_ITEM B WHERE A."+sp.getConditionId()+" = B."+sp.getConditionId()+
                    " @WHERE_1@ ";
            for(String k: ikey){
                if(itab.isFkField(k)){
                    ConFieldM f=new ConFieldM(k, itab, session, "pp_operate", "B", formJSON);
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            dselect=dselect+")</if>";
            ztabSql="<rule from=\"A."+tableName+"\"  from1=\"B."+tableitemName+"\"/>"+role+" SELECT "+select+" FROM "+tableName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return getRoleString("zTab", "pp_operate")+ztabSql;
    }

    public String getmxTabSql() throws Exception {
        if(mxtabSql==null){
            String select="";
            String role="";
            String dselect="";
            int len=ikey.size();
            for(int i=0; i<len; i++){
                String k=ikey.get(i);
                if(!"SYS_RQ".equals(k) && !"Z_ID".equals(k)){
                    ConFieldM f=new ConFieldM(k, itab, session, "pp_operate", "A", formJSON);
                    if(!"".equals(select)){
                        select=select+",";
                    }
                    select=select+f.getSqlData().getSelect();
                    dselect=dselect+f.getSqlData().getWhere();
                    role=role+f.getRole();
                }
            }
            mxtabSql="<rule from=\"A."+tableitemName+"\" />"+role+" SELECT "+select+" FROM "+tableitemName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return mxtabSql;
    }

    public String getzTabSql_Single() throws Exception {  //单条主表查询
        if(ztabSqlSingle==null){
            String select="";
            String role="";
            String dselect="";
            int len=zkey.size();
            for(int i=0; i<len; i++){
                String k=zkey.get(i);
                if(!sp.getVersion().equals(k) && !sp.getScene().equals(k) && !sp.getBilltype().equals(k) && !"SYS_RQ".equals(k) && !"Z_ID".equals(k) ){
                    if(ztab.isField(k)){
                        ConFieldM f=new ConFieldM(k, ztab, session, "pp_operate", "A", formJSON);
                        if(!"".equals(select)){
                            select=select+",";
                        }
                        select=select+f.getSqlData().getSelect();
                        dselect=dselect+f.getSqlData().getWhere();
                        role=role+f.getRole();
                    }
                }
            }
            select=select+",(SELECT Z.ID FROM EXE_CONDITION_SUM Z WHERE A.ID=Z.B_ID AND Z.CONDITION_ID=#{CID} AND Z.DIMENSION_ID=#{MID} ) AS Z_ID ";
            ztabSqlSingle="<rule from=\"A."+tableName+"\" />"+role+" SELECT "+select+" FROM "+tableName+" A  WHERE 1=1 "+dselect+" @WHERE@ ";
        }
        return ztabSqlSingle;
    }

}
